﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Threading;
using KOPLibrary.Forms;
using System.IO;
using System.Reflection;
using System.Collections;
using System.Security.Principal;

namespace KOPLibrary.DataManagement.Cacheflow
{
    public enum ExportType
    {
        CreateNew,
        UpdateExsiting,
        DataOnly
    }
    public class ExcelExporter
    {
        public const string DataSheetName = "Nakit Akış Verileri";
        private Microsoft.Office.Interop.Excel.Application app;
        public ExportType ExportType { get; set; }
        private static string[] Months = new string[] {
            "Oca",
            "Şub",
            "Mar",
            "Nis",
            "May",
            "Haz",
            "Tem",
            "Ağu",
            "Eyl",
            "Eki",
            "Kas",
            "Ara"
        };
        private static string[] Headers = new string[] 
        {
            "Tarih",
            "Ana Çıkış Hesap",
            "Detay Çıkış Hesap",
            "Çıkış Hesap",
            "Ana Giriş Hesap",
            "Detay Giriş Hesap",
            "Giriş Hesap",
            "Tip",
            "Etiket1",
            "Etiket2",
            "Etiket3",
            "Olasılık Yüzdesi",
            "Tölerans Yüzdesi",
            "Giren Miktar",
            "Çıkan Miktar",
            "Enaz Giren Miktar",
            "Enaz Çıkan Miktar",
            "Ençok Giren Miktar",
            "Ençok Çıkan Miktar",
            "Bakiye Miktar",
            "Enaz Bakiye",
            "Ençok Bakiye",
            "Gün",
            "Ay",
            "Yıl"
        };

        public ExcelExporter(Microsoft.Office.Interop.Excel.Application app, ExportType type)
        {
            this.app = app;
            ExportType = type;



        }

        public void CreateAnalyse(DataView view)
        {
            System.Globalization.CultureInfo ciSave = Thread.CurrentThread.CurrentCulture;
            System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
            Thread.CurrentThread.CurrentCulture = ci;

            app.ScreenUpdating = false;
            try
            {
                if (ExportType == Cacheflow.ExportType.DataOnly)
                {
                    Workbook wBook = (Workbook)app.Workbooks.Add();
                    Worksheet sheet = (Worksheet)wBook.Worksheets.Add();
                    sheet.Name = DataSheetName;
                    CreateHeaders(sheet);
                    CreateData(sheet, view);
                }
                else
                {
                    Workbook wBook;
                    if (ExportType == Cacheflow.ExportType.CreateNew)
                    {
                        string fileName = Path.GetTempFileName() + ".xlsm";

                        using (Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("KOPLibrary.Resources.Nakit Akış Analizi.xlsm"))
                        {


                            using (FileStream file = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite))
                            {
                                byte[] content = new byte[stream.Length];
                                stream.Read(content, 0, (int)stream.Length);
                                file.Write(content, 0, content.Length);
                                file.Close();
                            }

                            stream.Close();
                        }
                        ExcelManager mng = new ExcelManager(app);
                        wBook = mng.OpenDocument(fileName);
                    }
                    else wBook = app.ActiveWorkbook;
                    UpdateCurrentDocument(wBook, view);
                }
            }
            finally
            {
                app.ScreenUpdating = true;
                Thread.CurrentThread.CurrentCulture = ciSave;
            }

        }

        private void UpdateCurrentDocument(Workbook wBook, DataView view)
        {
            Worksheet sheet;
            try
            {
                sheet = (Worksheet)wBook.Sheets[DataSheetName];
            }
            catch
            {
                return;
            }
            sheet.Cells.Clear();
            CreateHeaders(sheet);
            CreateData(sheet, view);
            string sourceRange = string.Format("{0}!R1C1:R{1}C{2}", sheet.Name, view.Count * 2 + 1, Headers.Length);

            SlicerCaches scaches = wBook.SlicerCaches;


            System.Collections.Generic.Dictionary<SlicerCache, List<PivotTable>> change = new Dictionary<SlicerCache, List<PivotTable>>();

            foreach (SlicerCache sl in scaches)
            {

                change.Add(sl, new List<PivotTable>());
                List<PivotTable> pivots = new List<PivotTable>();
                foreach (PivotTable t in sl.PivotTables)
                {
                    pivots.Add(t);
                    change[sl].Add(t);
                }
                foreach (PivotTable t in pivots)
                {
    
                    //if (!t.PivotCache().RefreshOnFileOpen)
                    //    t.PivotCache().Refresh();
                    try
                    {
                        sl.PivotTables.RemovePivotTable(t);
                    }
                    catch
                    {
                        t.PivotCache().Refresh();
                        sl.PivotTables.RemovePivotTable(t);
                    }
                }

            }

            PivotCache cache = wBook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, SourceData: sourceRange);           
            cache.RefreshOnFileOpen = true;
            int cacheIndex = -1;
            foreach (Worksheet s in wBook.Sheets)
            {
                PivotTables pivots = (PivotTables)s.PivotTables();


                foreach (PivotTable t in pivots)
                {


                    //Slicers slicers = (Slicers)t.Slicers;
                    //List<Slicer> slList = new List<Slicer>(slicers.Count);

                    //foreach (Slicer sl in slicers)
                    //{
                    //    slList.Add(sl);
                    //    sl.SlicerCache.PivotTables.RemovePivotTable(t);
                    //}

                    //if (!t.PivotCache().RefreshOnFileOpen)
                    //    t.PivotCache().Refresh();

                    if (cacheIndex < 0)
                    {
                        t.ChangePivotCache(cache);
                        cache.Refresh();
                        cacheIndex = t.CacheIndex;
                    }
                    else t.CacheIndex = cacheIndex;
                    
                                    
                    //foreach (Slicer sl in slList)
                    //{
                    //    sl.SlicerCache.PivotTables.AddPivotTable(t);
                    //}

                    //t.PivotCache().Refresh();
                }
                if (s.Name == "Boş Analiz Sayfası")
                {
                    try
                    {
                        string[] parts = WindowsIdentity.GetCurrent().Name.Split('\\');
                        if (parts.Length > 0)
                            s.Name = parts[parts.Length - 1] + " Analiz Sayfası";
                    }
                    catch
                    {
                    }
                }

            }
            foreach (KeyValuePair<SlicerCache, List<PivotTable>> val in change)
            {
                foreach (PivotTable t in val.Value)
                {
                    t.SaveData = true;
                    
                    val.Key.PivotTables.AddPivotTable(t);

                }
            }
        }

        //private void CreatePivot(Worksheet sheet, Workbook wBook, DataView view)
        //{
        //    if (ExportType == Cacheflow.ExportType.PivotAndChart || ExportType == Cacheflow.ExportType.PivotOnly)
        //    {
        //        string sourceRange = string.Format("{0}!R1C1:R{1}C{2}", sheet.Name, view.Count * 2 + 1, Headers.Length);
        //        Worksheet destSheet = (Worksheet)wBook.Worksheets.Add();

        //        PivotCache cache = wBook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, SourceData: sourceRange);
        //        PivotTable pivot = cache.CreatePivotTable(TableDestination: string.Format("{0}!R3C1", destSheet.Name), TableName: "PivotTable1");
        //        destSheet.Select();
        //        ((Range)destSheet.Cells[3, 1]).Select();
        //        destSheet.Name = "Nakit Akış Analizi";

        //        if (ExportType == Cacheflow.ExportType.PivotAndChart)
        //        {
        //            Shape chart = destSheet.Shapes.AddChart();
        //            chart.Name = "Grafik";
        //            chart.Select();
        //            chart.Chart.ChartType = XlChartType.xlColumnClustered;
        //        }
        //        else
        //        {

        //            PivotField tarihField = pivot.PivotFields("Tarih") as PivotField;
        //            tarihField.Orientation = XlPivotFieldOrientation.xlRowField;
        //            tarihField.Position = 1;
        //            if (tarihField.DataRange.Count > 0)
        //            {
        //                destSheet.get_Range("A4").Select();
        //                Range r = (Range)app.Selection;
        //                r.Group(Start: true, End: true, Periods: new object[] { false, false, false, false, true, false, true });
        //                PivotField yearField = pivot.PivotFields("Years") as PivotField;
        //                yearField.Caption = "Yıl";
        //                yearField.Orientation = XlPivotFieldOrientation.xlColumnField;
        //                yearField.Position = 1;
        //            }
        //            tarihField.Orientation = XlPivotFieldOrientation.xlColumnField;
        //            PivotField kaynakField = pivot.PivotFields("Kaynak") as PivotField;
        //            kaynakField.Orientation = XlPivotFieldOrientation.xlRowField;
        //            kaynakField.Position = 1;

        //            //PivotField toplamGiren = pivot.AddDataField(pivot.PivotFields("Giren Miktar"), Caption: "Giren");
        //            //toplamGiren.Function = XlConsolidationFunction.xlSum;                    
        //            //PivotField toplamCikan = pivot.AddDataField(pivot.PivotFields("Çıkan Miktar"), Caption: "Çıkan");
        //            //toplamCikan.Function = XlConsolidationFunction.xlSum;

        //            PivotField toplamBakiye = pivot.AddDataField(pivot.PivotFields("Bakiye Miktar"), Caption: "Bakiye");
        //            toplamBakiye.Function = XlConsolidationFunction.xlSum;
        //            toplamBakiye.NumberFormat = "#,##0.00 $";

        //            SlicerCache sCache2 = wBook.SlicerCaches.Add(pivot, "Tarih");
        //            sCache2.Slicers.Add(destSheet, Caption: "Tarih");

        //            SlicerCache sCache3 = wBook.SlicerCaches.Add(pivot, "Years");
        //            sCache3.Slicers.Add(destSheet, Caption: "Yıl");

        //            SlicerCache sCache1 = wBook.SlicerCaches.Add(pivot, "Kaynak");
        //            sCache1.Slicers.Add(destSheet, Caption: "Kaynak");





        //        }
        //    }
        //}

        private void CreateData(Worksheet sheet, DataView view)
        {
            ProgressForm p = new ProgressForm();
            p.Initialize(1, view.Count, "İşleme başlanılıyor ...", false, null);
            int row = 2;
            try
            {
                for (int i = 0; i < view.Count; i++)
                {
                    ExportRow(sheet, row++, (global::KOPLibrary.Cacheflow.CacheflowDs.HareketRow)view[i].Row, true);
                    p.Update("İşlem sürüyor ...");
                    if (ExportType != Cacheflow.ExportType.DataOnly)
                        ExportRow(sheet, row++, (global::KOPLibrary.Cacheflow.CacheflowDs.HareketRow)view[i].Row, false);
                }
                Range dataRange = sheet.Range["A2"].get_Resize(RowSize: view.Count * 2, ColumnSize: 1);
                dataRange.NumberFormat = "m/d/yyyy";
                dataRange = sheet.Range["N2"].get_Resize(RowSize: view.Count * 2, ColumnSize: 9);
                dataRange.NumberFormat = "#,##0.00 $";
            }
            finally
            {
                p.Done();
            }
        }

        private string ReverseTip(string tip)
        {
            if (tip == "Gelir")
                return "Gider";
            else if (tip == "Gider")
                return "Gelir";
            else return tip;
        }

        private void ExportRow(Worksheet sheet, int row, KOPLibrary.Cacheflow.CacheflowDs.HareketRow r, bool normal)
        {
            object[] data = new object[Headers.Length];
            for (int i = 0; i < Headers.Length; i++)
            {
                Range cell = (Range)sheet.Cells[row, i + 1];
                switch (i)
                {
                    case 0:
                        {
                            data[i] = r.Tarih.Date;
                            break;
                        }
                    case 1:
                        {
                            data[i] = normal ? r.KaynakAnaHesapAd : r.HedefAnaHesapAd;
                            break;
                        }
                    case 2:
                        {
                            data[i] = normal ? r.KaynakHesapAd : r.HedefHesapAd;
                            break;
                        }
                    case 3:
                        {
                            data[i] = normal ? r.KaynakAd : r.HedefAd;
                            break;
                        }
                    case 4:
                        {
                            data[i] = normal ? r.HedefAnaHesapAd : r.KaynakAnaHesapAd;
                            break;
                        }
                    case 5:
                        {
                            data[i] = normal ? r.HedefHesapAd : r.KaynakHesapAd;
                            break;
                        }
                    case 6:
                        {
                            data[i] = normal ? r.HedefAd : r.KaynakAd;
                            break;
                        }
                    case 7:
                        {
                            data[i] = normal ? r.Tip : r.Tip;
                            break;
                        }
                    case 8:
                        {
                            data[i] = r.Etiket1;
                            break;
                        }
                    case 9:
                        {
                            data[i] = r.Etiket2;
                            break;
                        }
                    case 10:
                        {
                            data[i] = r.Etiket3;
                            break;
                        }
                    case 11:
                        {
                            data[i] = r.IsOlasılık_YüzdesiNull() ? 100 : r.Olasılık_Yüzdesi;
                            break;
                        }
                    case 12:
                        {
                            data[i] = r.IsTölerans_YüzdesiNull() ? 0 : r.Tölerans_Yüzdesi;
                            break;
                        }
                    case 13:
                        {
                            data[i] = normal ? 0 : r.Tutar;
                            break;
                        }
                    case 14:
                        {
                            data[i] = normal ? r.Tutar : 0;
                            break;
                        }
                    case 15:
                        {
                            cell.Formula = string.Format("=N{0}-N{0}*((100-L{0})/100)", row);
                            break;
                        }
                    case 16:
                        {
                            cell.Formula = string.Format("=O{0}-(O{0}*(100-L{0})/100)", row);
                            break;
                        }
                    case 17:
                        {
                            cell.Formula = string.Format("=N{0}+N{0}*((100-L{0})/100)", row);
                            break;
                        }
                    case 18:
                        {
                            cell.Formula = string.Format("=O{0}+(O{0}*(100-L{0})/100)", row);
                            break;
                        }
                    case 19:
                        {
                            cell.Formula = string.Format("=N{0}-O{0}", row);
                            break;
                        }
                    case 20:
                        {
                            cell.Formula = string.Format("=P{0}-Q{0}", row);
                            break;
                        }
                    case 21:
                        {
                            cell.Formula = string.Format("=R{0}-S{0}", row);
                            break;
                        }
                    case 22:
                        {
                            cell.Value2 = r.Tarih.Date.Day;
                            break;
                        }
                    case 23:
                        {
                            cell.Value2 = Months[r.Tarih.Month - 1];
                            break;
                        }
                    case 24:
                        {
                            cell.Value2 = r.Tarih.Date.Year;
                            break;
                        }
                }


            }
            Range dataRange = ((Range)sheet.Cells[row, 1]).get_Resize(RowSize: 1, ColumnSize: 15);
            dataRange.Value = data;
        }

        private void CreateHeaders(Worksheet sheet)
        {
            object[] data = new object[Headers.Length];
            Range dataRange = (Range)sheet.Range["A1"].get_Resize(RowSize: 1, ColumnSize: Headers.Length);
            dataRange.Value = Headers;
            dataRange.Columns.AutoFit();

        }
    }
}
